<?php

/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2017/1/13
 * Time: 11:12
 */
namespace lib;

class Model
{
    protected $config = [
        'host'=>'localhost',
        'db'=>'liexin',
        'user'=>'root',
        'pass'=>'root',
        'pre'=>'lie_',
        'port'=>3306,
    ];
    /**
     * @var \PDO
     */
    static protected $db;
    public $tableName;
    protected $fields = [];
    protected $pk;
    protected $defaultVal = [];

    protected $options = [];

    public $data = [];
    public $pkVal;
    public $sql;
    public $executeTime = 0;
    public $error = '';
    public $errorCode = 0;

    public function __construct($config = [])
    {
        if(!Model::$db){
            $config = $config ? $config : App::$config['db'];
            if(!$config) {
                throw new \Exception('database connect failed.');
            }
            //配置DNS
            $this->config['host'] = $config['host'];
            $this->config['db'] = $config['name'];
            $this->config['user'] = $config['user'];
            $this->config['pass'] = $config['pass'];
            $this->config['pre'] = $config['prefix'];
            $this->config['port'] = $config['port'];

            $dsn = "mysql:host={$this->config['host']};port={$this->config['port']};dbname={$this->config['db']}";
            Model::$db = new \PDO($dsn, $this->config['user'], $this->config['pass'], [\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8;"]);
        }
    }

    static public function close()
    {
        //断开连接
        Model::$db = null;
    }

    /**
     * 设置表名
     * @param string $tableName
     * @return $this
     * @throws \Exception
     */
    public function table($tableName)
    {
        if($tableName){
            $this->tableName = $this->config['pre'].$tableName;
        } else {
            throw new \Exception('Table Name is empty');
        }

        //初始化表字段
        $fields = $this->query("SHOW COLUMNS FROM `{$this->tableName}`");
        foreach($fields as $field){
            $this->fields[] = $field['Field'];
            if($field['Key'] == 'PRI'){
                $this->pk = $field['Field'];
            }
            $this->defaultVal[$field['Field']] = $field['Default'] ? $field['Default'] : null;
        }

        return $this;
    }

    /**
     * 连接数据库
     * @param $dsn
     * @param $user
     * @param $pass
     */
    public function connect($dsn, $user, $pass)
    {
        self::$db = new \PDO($dsn, $user, $pass, [\PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8;"]);
    }


    /**
     * 执行查询语句
     * @param $sql
     * @return array|bool
     * @throws \Exception
     */
    public function query($sql)
    {
        $this->sql = $sql;
        if(get_class(Model::$db) != "PDO"){
            throw new \Exception('DB object is '.get_class(Model::$db));
        }
        $startTime = microtime(true);
        $stmt = self::$db->query($this->sql);
        $endTime = microtime(true);
        $this->executeTime = $endTime - $startTime;
        log_append($this->executeTime.'   '.$this->sql, ROOT_PATH.'logs/sql/'.date('YmdH').'.log');
        if(!$stmt){
            $this->error();
        }

        $result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        unset($stmt);
        return $result;
    }

    /**
     * 错误处理
     * @throws \Exception
     */
    public function error()
    {
        $error = Model::$db->errorInfo();
        $this->error = $error ? "{$error[0]} : {$error[1]} {$error[2]}.\r\n{$this->sql}\r\n" : '未知错误';
        $this->errorCode = $error[1];
        sp_error_log($this->error);
        throw new \Exception($this->error, $error[1]);
    }

    /**
     * 获取错误
     * @return string
     */
    public function getError()
    {
        return $this->error;
    }

    /**
     * 执行语句
     * @param $sql
     * @return int
     * @throws \Exception
     */
    public function exec($sql)
    {
        $this->sql = $sql;
        $startTime = microtime(true);
        $affect = Model::$db->exec($sql);
        $endTime = microtime(true);
        $this->executeTime = $endTime - $startTime;

        log_append($this->executeTime.'   '.$this->sql, ROOT_PATH.'logs/sql/'.date('YmdH').'.log');
        if($affect === false){
            $this->error();
        }
        return $affect;
    }

    public function optionsEmpty()
    {
        $this->options['field'] = null;
        $this->options['where'] = null;
        $this->options['order'] = null;
        $this->options['limit'] = null;
        $this->options['join'] = null;
    }

    /**
     * 查询
     * @return array|bool
     */
    public function select()
    {
        $field = isset($this->options['field']) && $this->options['field'] ? $this->options['field'] : '*';
        $where = isset($this->options['where']) && $this->options['where'] ? 'WHERE '.$this->options['where'] : '';
        $order = isset($this->options['order']) && $this->options['order'] ? 'ORDER BY '.$this->options['order'] : '';
        $limit = isset($this->options['limit']) && $this->options['limit'] ? 'LIMIT '.$this->options['limit'] : '';
        $join = isset($this->options['join']) && $this->options['join'] ? $this->options['join'] : '';
        $sql = "SELECT {$field} FROM {$this->tableName} {$join} {$where} {$order} {$limit}";
        $result = $this->query($sql);
        log_append($this->sql, ROOT_PATH.'logs/sql/'.date('YmdH').'.log');
        $this->optionsEmpty();
        return $result;
    }

    /**
     * 查询单条
     * @param null $pk
     * @return array|bool
     */
    public function find($pk = null)
    {
        $field = isset($this->options['field']) && $this->options['field'] ? $this->options['field'] : '*';
        $where = isset($this->options['where']) && $this->options['where'] ? 'WHERE '.$this->options['where'] : '';
        $order = isset($this->options['order']) && $this->options['order'] ? 'ORDER BY '.$this->options['order'] : '';
        $limit = isset($this->options['limit']) && $this->options['limit'] ? 'LIMIT '.$this->options['limit'] : '';
        $join = isset($this->options['join']) && $this->options['join'] ? $this->options['join'] : '';
        if($pk && $this->pk){
            $where = $where ? $where." AND `{$this->pk}` = '{$pk}'" : "WHERE `{$this->pk}` = '{$pk}'";
        }

        $sql = "SELECT {$field} FROM `{$this->tableName}` {$join} {$where} {$order} {$limit}";
        $result = $this->query($sql);
        $this->optionsEmpty();
        return $result ? $result[0] : null;
    }

    /**
     * 统计总数
     * @return int
     */
    public function count()
    {
        $field = 'COUNT(*) AS cnt';
        $where = isset($this->options['where']) && $this->options['where'] ? 'WHERE '.$this->options['where'] : '';
        $join = isset($this->options['join']) && $this->options['join'] ? $this->options['join'] : '';

        $sql = "SELECT {$field} FROM `{$this->tableName}` {$join} {$where}";
        $result = $this->query($sql);
        $this->optionsEmpty();
        return $result ? $result[0]['cnt'] : 0;
    }



    /**
     * @param $field
     * @param bool $isList
     * @return mixed|null
     */
    public function getField($field, $isList = false)
    {
        $first = explode(',', $field);
        $first = trim($first[0]);
        $this->options['field'] = isset($this->options['field']) && $this->options['field'] ? $this->options['field'].','.$field : $field;
        $field = isset($this->options['field']) && $this->options['field'] ? $this->options['field'] : $this->pk;
        $where = isset($this->options['where']) && $this->options['where'] ? 'WHERE '.$this->options['where'] : '';
        $order = isset($this->options['order']) && $this->options['order'] ? 'ORDER BY '.$this->options['order'] : '';
        $limit = isset($this->options['limit']) && $this->options['limit'] ? 'LIMIT '.$this->options['limit'] : '';

        if(!$isList){
            $limit = 'LIMIT 1';
        }
        $join = isset($this->options['join']) && $this->options['join'] ? $this->options['join'] : '';

        $sql = "SELECT {$field} FROM `{$this->tableName}` {$join} {$where} {$order} {$limit}";
        $result = $this->query($sql);
        $res = [];
        $fields = array_map('trim', explode(',', $field));
        $fieldCnt = count($fields);
        foreach($result as $item)
        {

            if($fieldCnt > 1){
                if($isList){
                    $res[$item[$first]] = $fieldCnt == 2 ? $item[$fields[1]] : $item;
                } else {
                    $res[] = $item;
                    break;
                }
            } else {
                $res[] = $item[trim($field)];
                if(!$isList){
                    break;
                }
            }
        }

        $this->optionsEmpty();
        return $isList ? $res : $res[0];
    }

    /**
     * 保存数据
     * @param $data
     * @return int
     */
    public function save($data)
    {
        $data = $this->create($data);
        if($this->pkVal){
//            if($this->where([$this->pk=>$this->pkVal])->count()){
//                $this->andWhere("`{$this->pk}` = $this->pkVal");
//                return $this->update($data);
//            } else {
//                return $this->add($data);
//            }
            $this->andWhere("`{$this->pk}` = $this->pkVal");
            return $this->update($data);
        }
        return $this->update($data);
    }

    /**
     * 更新数据
     * @param $data
     * @return int
     */
    public function update($data)
    {
        $fieldVal = [];
        if(is_array($data)){
            foreach($data as $key => $value){
                if($key == $this->pk){
                    continue;
                }
                if(in_array($key, $this->fields)){
                    $fieldVal[] = "`{$key}` = '".addslashes($value)."'";
                }
            }
        }
        $set = join(' , ', $fieldVal);
        $where = isset($this->options['where']) && $this->options['where'] ? 'WHERE '.$this->options['where'] : '';

        $sql = "UPDATE `{$this->tableName}` SET {$set} {$where}";
        $this->optionsEmpty();
        return $this->exec($sql);
    }

    /**
     * 添加数据
     * @param $data
     * @return int
     */
    public function add($data = null)
    {
        $field = [];
        $value = [];
        if($data == null){
            $data = $this->data;
        }
        foreach($data as $key => $val){
            if(in_array($key, $this->fields)){
                $field[] = "`{$key}`";
                $value[] = "'".addslashes($val)."'";
            }
        }

        $fields = join(',', $field);
        $values = join(',', $value);

        $sql = "INSERT INTO `{$this->tableName}` ($fields) VALUES ($values)";
        $this->optionsEmpty();
        return $this->exec($sql) ? ( Model::$db->lastInsertId() ? Model::$db->lastInsertId() : true ) : false;
    }

    /**
     * 批量添加
     * @param null $data
     * @return int
     * @throws \Exception
     */
    public function addAll($data = null)
    {
        if($data == null || !is_array($data)){
            throw new \Exception('Data is invalid.');
        }
        $field = [];
        $value = [];
        //插入字段处理
        foreach($data as $item){
            foreach($item as $key => $val){
                if(in_array($key, $this->fields) && !in_array($key, $field)){
                    $field[] = $key;
                }
            }
        }
        $field = array_unique($field);
        //插入数据处理
        foreach($data as $item){
            $tmp = [];
            foreach($field as $key){
                $default = $this->defaultVal[$key] ? $this->defaultVal[$key] : 'NULL';
                $tmp[] = isset($item[$key]) ? "'{$item[$key]}'" : $default;
            }
            if($tmp){
                $value[] = $tmp;
            }
        }

        $field = array_map(function($val){
            return "`".addslashes($val)."`";
        }, $field);


        $fields = join(',', $field);
        $values = join(',', array_map(function($val){
            return "(".join(',', $val).")";
        }, $value));
        $sql = "INSERT INTO `{$this->tableName}` ($fields) VALUES $values";
        $this->optionsEmpty();
        return $this->exec($sql);

    }

    /**
     * 删除数据
     * @param null $pk
     * @return int
     * @throws \Exception
     */
    public function delete($pk = null)
    {
        $where = isset($this->options['where']) && $this->options['where'] ? 'WHERE '.$this->options['where'] : '';
        if($pk) {
            $where = $where ? $where." AND `{$this->pk}` = '{$pk}'" : "WHERE `{$this->pk}` = '{$pk}'";
        }
        if(!$where){
            throw new \Exception('Where is invalid.');
        }
        $sql = "DELETE FROM `{$this->tableName}` {$where}";
        $this->optionsEmpty();
        return $this->exec($sql);
    }

    /**
     * 自动化数据
     * @param array $data
     * @return array|bool
     */
    public function create($data)
    {
        if(!is_array($data)){
            $this->error = '数据不能为空';
            return false;
        }
        foreach($data as $key => $value){
            if(in_array($key, $this->fields)){
                $this->data[$key] = $value;
                if($key == $this->pk && $value){
                    $this->pkVal = $value;
                }
                if($key == $this->pk && !$value){
                    unset($data[$key]);
                }
            }
        }

        if(!$this->data){
            $this->error = '数据初始化失败';
            return false;
        }
        return $this->data;
    }

    public function checkField()
    {

    }

    /**
     * 条件
     * @param string $where
     * @return $this
     * @throws \Exception
     */
    public function where($where = '')
    {
        if(!$where){
            return $this;
        }
        if(is_array($where)){
            $map = [];
            foreach($where as $field => $value){
                if(in_array($field, $this->fields)){
                    $map[] = "`{$field}` = '{$value}'";
                }
            }

            $this->options['where'] = join(' AND ', $map);
        }else if(is_string($where)){
            //主键
            if(preg_match('/^[a-zA-Z0-9]+$/', trim($where)) && $this->pk){
                $where = "`{$this->pk}` = '$where'";
            }
            $this->options['where'] = $where;
        } else {
            throw  new \Exception('Where is invalid.');
        }

        return $this;
    }

    /**
     * 添加条件
     * @param string $where
     */
    public function andWhere($where = ''){
        $this->options['where'] = isset($this->options['where']) && $this->options['where'] ? $this->options['where'].' AND '.$where : $where;
    }

    /**
     * limit限制条数
     * @param $limit
     * @return $this
     */
    public function limit($limit)
    {
        $this->options['limit'] = $limit;
        return $this;
    }

    /**
     * 排序
     * @param $order
     * @return $this
     */
    public function order($order)
    {
        $this->options['order'] = $order;
        return $this;
    }

    /**
     * 查询字段
     * @param string $field
     * @return $this
     */
    public function field($field)
    {
        $this->options['field'] = $field;
        return $this;
    }

    /**
     * 联表
     * @param $join
     * @return $this
     */
    public function join($join)
    {
        $this->options['join'] = $join;
        return $this;
    }

    public function getAutoIncrement()
    {
        $this->sql = "SELECT AUTO_INCREMENT FROM information_schema.tables WHERE table_name= '{$this->tableName}' AND `TABLE_SCHEMA` = '".DB_NAME."'";
    }

}